﻿

--------------------thong ke bao cao-------------------

ALTER PROC THONG_KE_TINH_HINH_DAT_NHAP
AS
SELECT A.SODH,A.NGAYDH,B.MAVTU,
(SELECT TENNHACC FROM NHACC WHERE NHACC.MANHACC=A.MANHACC) AS TENNHACC,
(SELECT TENVTU FROM VATTU WHERE VATTU.MAVTU=B.MAVTU) AS TENVATU,
ISNULL((SELECT SUM(SLDAT) FROM DONDH,CTDONDH WHERE DONDH.SODH=CTDONDH.SODH AND CTDONDH.MAVTU=B.MAVTU AND DONDH.SODH=A.SODH GROUP BY DONDH.SODH),0) AS SL_DAT,
ISNULL((SELECT SUM(SLNHAP)	FROM PNHAP, CTPNHAP	WHERE PNHAP.SOPN=CTPNHAP.SOPN AND CTPNHAP.MAVTU=B.MAVTU AND PNHAP.SODH=A.SODH	GROUP BY PNHAP.SODH),0) AS SL_NHAP,
(ISNULL((SELECT SUM(SLDAT) FROM DONDH,CTDONDH WHERE DONDH.SODH=CTDONDH.SODH AND CTDONDH.MAVTU=B.MAVTU AND DONDH.SODH=A.SODH GROUP BY DONDH.SODH),0)-IsNULL((SELECT SUM(SLNHAP) FROM PNHAP, CTPNHAP WHERE PNHAP.SOPN=CTPNHAP.SOPN AND CTPNHAP.MAVTU=B.MAVTU AND PNHAP.SODH=A.SODH	GROUP BY PNHAP.SODH),0)) AS SL_CHUAGIAO
FROM DONDH A,CTDONDH B
WHERE A.SODH=B.SODH

GO

ALTER PROC THONG_KE_TINH_HINH_DAT_NHAP_THEO_SODH
@SODH CHAR(4)
AS
SELECT A.SODH,A.NGAYDH,B.MAVTU,
(SELECT TENNHACC FROM NHACC WHERE NHACC.MANHACC=A.MANHACC) AS TENNHACC,
(SELECT TENVTU FROM VATTU WHERE VATTU.MAVTU=B.MAVTU) AS TENVATU,
ISNULL((SELECT SUM(SLDAT) FROM DONDH,CTDONDH WHERE DONDH.SODH=CTDONDH.SODH AND CTDONDH.MAVTU=B.MAVTU AND DONDH.SODH=A.SODH GROUP BY DONDH.SODH),0) AS SL_DAT,
ISNULL((SELECT SUM(SLNHAP)	FROM PNHAP, CTPNHAP	WHERE PNHAP.SOPN=CTPNHAP.SOPN AND CTPNHAP.MAVTU=B.MAVTU AND PNHAP.SODH=A.SODH	GROUP BY PNHAP.SODH),0) AS SL_NHAP,
(ISNULL((SELECT SUM(SLDAT) FROM DONDH,CTDONDH WHERE DONDH.SODH=CTDONDH.SODH AND CTDONDH.MAVTU=B.MAVTU AND DONDH.SODH=A.SODH GROUP BY DONDH.SODH),0)-IsNULL((SELECT SUM(SLNHAP) FROM PNHAP, CTPNHAP WHERE PNHAP.SOPN=CTPNHAP.SOPN AND CTPNHAP.MAVTU=B.MAVTU AND PNHAP.SODH=A.SODH	GROUP BY PNHAP.SODH),0)) AS SL_CHUAGIAO
FROM DONDH A,CTDONDH B
WHERE A.SODH=B.SODH AND A.SODH=@SODH

GO

CREATE PROC THONG_KE_TONG_SL_DAT_NHAP AS
	SELECT A.SODH,A.NGAYDH,A.MANHACC,TENNHACC, ISNULL(SUM(SLDAT),0) AS TONG_SL_DAT,
		 ISNULL((SELECT SUM(SLNHAP)	FROM PNHAP, CTPNHAP WHERE PNHAP.SOPN=CTPNHAP.SOPN AND PNHAP.SODH=A.SODH GROUP BY SODH),0) AS TONG_SL_NHAP
	FROM DONDH A,CTDONDH B,NHACC
	WHERE A.SODH=B.SODH AND A.MANHACC=NHACC.MANHACC
	GROUP BY A.SODH,A.NGAYDH,A.MANHACC,TENNHACC

--g.	Tạo view có tên sp_TongNhap bao gồm các thông tin sau : năm tháng, mã vật tư, tổng số lượng nhập.
--View này dùng để thống kê tổng số lượng nhập của các vật tư trong từng năm tháng (năm tháng có dạng YYYY-MM). Chú ý: không sử dụng bảng TONKHO.
ALTER PROC sp_TONGNHAP 
@FROMDATE DATETIME,
@TODATE DATETIME,
@MAVTU CHAR(4)
AS
	IF (@MAVTU IS NULL) OR (@MAVTU='')
	BEGIN
		SELECT  CONVERT(NVARCHAR,YEAR(NGAYNHAP))+ '/'+ CONVERT(NVARCHAR,MONTH(NGAYNHAP)) AS NAMTHANG, B.MAVTU,C.TENVTU,SUM(SLNHAP) AS TONG_SL
		FROM PNHAP A,CTPNHAP B,VATTU C
		WHERE A.SOPN=B.SOPN AND C.MAVTU=B.MAVTU AND NGAYNHAP >=@FROMDATE AND NGAYNHAP<=@TODATE
		GROUP BY B.MAVTU,C.TENVTU,CONVERT(NVARCHAR,YEAR(NGAYNHAP))+ '/'+ CONVERT(NVARCHAR,MONTH(NGAYNHAP))
	END
	ELSE
	BEGIN
		SELECT  CONVERT(NVARCHAR,YEAR(NGAYNHAP))+ '/'+ CONVERT(NVARCHAR,MONTH(NGAYNHAP)) AS NAMTHANG, B.MAVTU,C.TENVTU,SUM(SLNHAP) AS TONG_SL
		FROM PNHAP A,CTPNHAP B,VATTU C
		WHERE A.SOPN=B.SOPN AND C.MAVTU=B.MAVTU AND B.MAVTU=@MAVTU AND NGAYNHAP >=@FROMDATE AND NGAYNHAP<=@TODATE 
		GROUP BY B.MAVTU,C.TENVTU,CONVERT(NVARCHAR,YEAR(NGAYNHAP))+ '/'+ CONVERT(NVARCHAR,MONTH(NGAYNHAP))
	END
GO

exec sp_TongNhap '1/1/2011','12/31/2011',''
--h.	Tạo view có tên sp_TongXuat bao gồm các thông tin sau : năm tháng, mã vật tư, tổng số lượng xuất. 
--View này dùng để thống kê tổng số lượng xuất của các vật tư trong từng năm tháng (năm tháng có dạng YYYY-MM). Chú ý: không sử dụng bảng TONKHO.
CREATE PROC sp_TONGXUAT 
@FROMDATE DATETIME,
@TODATE DATETIME,
@MAVTU CHAR(4)
AS
	IF (@MAVTU IS NULL) OR (@MAVTU='')
	BEGIN
		SELECT CONVERT(NVARCHAR,YEAR(NGAYXUAT)) +'/'+ CONVERT(NVARCHAR,MONTH(NGAYXUAT)) AS NAMTHANG, B.MAVTU,C.TENVTU,SUM(SLXUAT) AS TONG_SL
		FROM PXUAT A,CTPXUAT B,VATTU C
		WHERE A.SOPX=B.SOPX AND C.MAVTU=B.MAVTU AND NGAYXUAT >=@FROMDATE AND NGAYXUAT<=@TODATE
		GROUP BY B.MAVTU,C.TENVTU,CONVERT(NVARCHAR,YEAR(NGAYXUAT))+ '/'+ CONVERT(NVARCHAR,MONTH(NGAYXUAT))
	END
	ELSE
	BEGIN
		SELECT  CONVERT(NVARCHAR,YEAR(NGAYXUAT)) +'/'+ CONVERT(NVARCHAR,MONTH(NGAYXUAT)) AS NAMTHANG, B.MAVTU,C.TENVTU,SUM(SLXUAT) AS TONG_SL
		FROM PXUAT A,CTPXUAT B,VATTU C
		WHERE A.SOPX=B.SOPX AND C.MAVTU=B.MAVTU AND B.MAVTU=@MAVTU AND NGAYXUAT >=@FROMDATE AND NGAYXUAT<=@TODATE
		GROUP BY B.MAVTU,C.TENVTU,CONVERT(NVARCHAR,YEAR(NGAYXUAT))+ '/'+ CONVERT(NVARCHAR,MONTH(NGAYXUAT))
	END
GO 


--danh sách các đơn đặt hàng chưa từng được nhập hàng.
CREATE Proc sp_DanhSachDONDHChuaTungNhapHang AS 
	SELECT SODH,VATTU.MAVTU,TENVTU ,SUM(SLDAT) AS TSL_DAT
	FROM CTDONDH,VATTU 
	WHERE CTDONDH.MAVTU=VATTU.MAVTU AND SODH NOT IN (SELECT SODH FROM PNHAP)
	GROUP BY SODH,VATTU.MAVTU,TENVTU
GO
--Cho biết danh sách các mặt hàng chưa từng được đặt hàng.
CREATE PROC sp_DanhSachVatTuChuaTungDatHang
AS
	SELECT *
	FROM VATTU
	WHERE MAVTU NOT IN (SELECT MAVTU FROM CTDONDH)
GO
--Cho biết các nhà cung cấp nàp có nhiều đơn đặt hàng nhất.
CREATE PROC sp_NhaCungCapCoNhieuDonDHNhat
AS
SELECT DONDH.MANHACC,NHACC.TENNHACC,DIACHI,DIENTHOAI, Count(DONDH.MANHACC) AS SOLANDAT
	FROM DONDH, NHACC
	WHERE DONDH.MANHACC=NHACC.MANHACC
	GROUP BY DONDH.MANHACC ,NHACC.TENNHACC,DIACHI,DIENTHOAI
	HAVING  COUNT(DONDH.MANHACC) = (SELECT TOP 1 COUNT(MANHACC) FROM DONDH GROUP BY MANHACC ORDER BY COUNT(MANHACC) DESC)
GO
--Cho biết vật tư nào có tổng số lượng xuất bán nhiều nhất.
CREATE VIEW vw_VATTU_TONGSLXUAT AS
	SELECT MAVTU,SUM(SLXUAT) AS TONGSLXUAT
	FROM CTPXUAT
	GROUP BY MAVTU
GO
ALTER PROC sp_VatTuCoSoLuongXuatNhieuNhat
AS
	SELECT VATTU.MAVTU,VATTU.TENVTU,DVTINH,PHANTRAM,HINH, SUM(SLXUAT) AS TSL_XUAT
	FROM CTPXUAT ,VATTU
	WHERE CTPXUAT.MAVTU=VATTU.MAVTU
	GROUP BY VATTU.MAVTU,VATTU.TENVTU,DVTINH,PHANTRAM,HINH
	HAVING SUM(SLXUAT) =  (SELECT MAX(TONGSLXUAT) FROM vw_VATTU_TONGSLXUAT)
--=============================================================================================
--TinhHinhNhapXuatVatTu
------------------------------------------------------------------------

--	Tạo view có tên vw_TongNhap bao gồm các thông tin sau : năm tháng, mã vật tư, tổng số lượng nhập.
--View này dùng để thống kê tổng số lượng nhập của các vật tư trong từng năm tháng (năm tháng có dạng YYYY-MM). Chú ý: không sử dụng bảng TONKHO.
CREATE VIEW vw_VatTuTongNhap AS
	SELECT MAVTU, CONVERT(NVARCHAR,YEAR(NGAYNHAP))+ '-'+ CONVERT(NVARCHAR,MONTH(NGAYNHAP)) AS NGAYNHAPHANG, SUM(SLNHAP) AS TONG_SL_NHAP
	FROM PNHAP A,CTPNHAP B
	WHERE A.SOPN=B.SOPN
	GROUP BY MAVTU, CONVERT(NVARCHAR,YEAR(NGAYNHAP))+ '-'+ CONVERT(NVARCHAR,MONTH(NGAYNHAP))
GO
--h.	Tạo view có tên vw_TongXuat bao gồm các thông tin sau : năm tháng, mã vật tư, tổng số lượng xuất. 
--View này dùng để thống kê tổng số lượng xuất của các vật tư trong từng năm tháng (năm tháng có dạng YYYY-MM). Chú ý: không sử dụng bảng TONKHO.
CREATE VIEW vw_VatTuTongXuat AS
	SELECT MAVTU,CONVERT(NVARCHAR,YEAR(NGAYXUAT)) +'-'+ CONVERT(NVARCHAR,MONTH(NGAYXUAT)) AS NGAYXUATHANG, SUM(SLXUAT) AS TONG_SL_XUAT
	FROM PXUAT A,CTPXUAT B
	WHERE A.SOPX=B.SOPX
	GROUP BY MAVTU,CONVERT(NVARCHAR,YEAR(NGAYXUAT)) +'-'+ CONVERT(NVARCHAR,MONTH(NGAYXUAT)) 
GO  
--f.	Cho biết tình hình nhập xuất của vật tư, thông tin gồm : năm tháng (năm tháng có dạng YYYY-MM), 
--mã vật tư, tên vật tư, tổng số lượng nhập,tổng số lượng xuất
CREATE PROC sp_TinhHinhNhapXuatVatTu
AS
SELECT VATTU.MAVTU,TENVTU,NGAYNHAPHANG AS NAMTHANG, TONG_SL_NHAP,TONG_SL_XUAT
from vw_VatTuTongNhap, VATTU,vw_VatTuTongXuat
WHERE vw_VatTuTongNhap.MAVTU=VATTU.MAVTU AND vw_VatTuTongXuat.MAVTU=VATTU.MAVTU 
		AND NGAYNHAPHANG=NGAYXUATHANG
UNION
SELECT VATTU.MAVTU,TENVTU,NGAYNHAPHANG AS NAMTHANG, TONG_SL_NHAP,0
from vw_VatTuTongNhap, VATTU
WHERE vw_VatTuTongNhap.MAVTU=VATTU.MAVTU AND vw_VatTuTongNhap.MAVTU NOT IN (SELECT MAVTU FROM vw_VatTuTongXuat)
UNION
SELECT VATTU.MAVTU,TENVTU,NGAYXUATHANG AS NAMTHANG,0, TONG_SL_XUAT
from vw_VatTuTongXuat, VATTU
WHERE vw_VatTuTongXuat.MAVTU=VATTU.MAVTU AND vw_VatTuTongXuat.MAVTU NOT IN (SELECT MAVTU FROM vw_VatTuTongNhap)
